package be.js.sqlCodeGenerator;

import java.lang.reflect.Constructor;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.StringUtils;

/**
 * Generates the sql code by calling an extended class of SQLTemplate
 * defined in the properties files thru reflection.
 * 
 * $Id: SQLCodeGenerator.java 81 2013-03-12 21:15:39Z johan.schamp@gmail.com $ 
 * @author johan schamp (johan.schamp@gmail.com)
 * 
 */
public class SQLCodeGenerator {
	public final static String TEMPLATESDIR = "templates";	// directory where template files are stored (input)
	public final static String TEMPLATESUFFIX = ".sql";		// suffix of template files
	public final static String GENERATEDDIR = "generated";	// directory where generated files are stored (output)
	public final static String GENERATEDSUFFIX = ".sql";		// suffix of generated files
	
	public static final String CRLF =  System.getProperty("line.separator");
	private String tableNamePattern = null;
	private String columnNamePattern = null;
	private String templateFile = null;
	private String templateClass = null;
	private DatabaseHelper databaseHelper = null;
	private String generatedFilePrefix = null;
	
	/**
	 * Constructor. Opens a connection to the database.
	 * 
	 * @param jdbcDriver the JDBC driver class
	 * @param jdbcConnectionUrl the connection url to the database instance
	 * @param jdbcUser the user name
	 * @param jdbcPassword the password
	 * @param schema the schema name
	 * @param tableNamePattern a pattern filtering the tables
	 * @param columnNamePattern a pattern filtering the columns
	 * @param templateFile an SQL template file name that is present in the 'templates' folder
	 * @param templateClass a template class name (fully qualified)
	 * @param generatedFilePrefix a prefix for the generated files in the 'generated' folder
	 * 
	 * @throws Exception
	 */
	public SQLCodeGenerator(
			String jdbcDriver,
			String jdbcConnectionUrl,
			String jdbcUser, 
			String jdbcPassword,
			String schema,
			String tableNamePattern,
			String columnNamePattern,
			String templateFile,
			String templateClass,
			String generatedFilePrefix
			) throws Exception {
		
		if(
				StringUtils.isEmpty(jdbcDriver)
				|| StringUtils.isEmpty(jdbcConnectionUrl)
				|| StringUtils.isEmpty(jdbcUser)
				|| StringUtils.isEmpty(jdbcPassword)
				|| StringUtils.isEmpty(schema)
				|| StringUtils.isEmpty(tableNamePattern)
				|| StringUtils.isEmpty(columnNamePattern)
				|| StringUtils.isEmpty(templateFile)
				|| StringUtils.isEmpty(templateClass)
				)
			throw new IllegalArgumentException();

		this.tableNamePattern = StringUtils.trim(tableNamePattern);
		this.columnNamePattern = StringUtils.trim(columnNamePattern);
		this.templateFile = StringUtils.trim(templateFile);
		this.templateClass = StringUtils.trim(templateClass);
		this.generatedFilePrefix = StringUtils.trim(generatedFilePrefix);
		
		this.databaseHelper = new DatabaseHelper(
				jdbcDriver, 
				jdbcConnectionUrl, 
				jdbcUser, 
				jdbcPassword, 
				schema);
		
	}

	/**
	 * Generates the code by calling the templateClass thru reflection
	 * with the given parameters for every table in the schema and
	 * according to the tableNamePattern / columnNamePattern.
	 * 
	 * @throws Exception
	 */
	public void generate() throws Exception {
		List<String> tables = getTables();
		
		for (int i = 0; i < tables.size(); i++){
			Main.LOGGER.debug("Table: " + tables.get(i));
			List<String> columns = getColumns(tables.get(i));
			for (int j = 0; j < columns.size(); j++){
				Main.LOGGER.debug(" - " + columns.get(j));
			}		
			Class<?> c = Class.forName(templateClass);
			Constructor ctor = c.getDeclaredConstructor(String.class, String.class, List.class, String.class);
			SQLTemplate sqlTemplate  = (SQLTemplate)ctor.newInstance(SQLCodeGenerator.TEMPLATESDIR + "/" + templateFile + SQLCodeGenerator.GENERATEDSUFFIX, tables.get(i), columns, generatedFilePrefix);

			
			Main.LOGGER.debug(sqlTemplate.getTemplateSQL());
			Main.LOGGER.info("Generating code for table '" + tables.get(i) + "' with template '" + templateFile +"'");
			sqlTemplate.generate();			
			Main.LOGGER.debug(sqlTemplate.getGeneratedSQL());

		}
		this.databaseHelper.closeQuitely();
		
	}
	

	/**
	 * Gets and returns a list of tables for the current connection.
	 * 
	 * @return a list of tables
	 * @throws SQLException
	 */
	private List<String> getTables() throws SQLException {
		
		ResultSet resultSet = this.databaseHelper.getTables();
		List<String> l = new ArrayList<String>();

		while (resultSet.next()) {
			
			String tableName = resultSet.getString("TABLE_NAME");
			
			if (tableName.matches(this.tableNamePattern)) {
				l.add(tableName);
			}			
		}
		DbUtils.closeQuietly(resultSet);
		return l;
	}

	/**
	 * Gets and returns a list of columns for a given table
	 *  for the current connection.
	 * 
	 * @param tableName the table name
	 * @return a list of columns
	 * @throws SQLException
	 */
	private List<String> getColumns(String tableName) throws SQLException {
		
		ResultSet resultSet = this.databaseHelper.getColumns(tableName);
		List<String> l = new ArrayList<String>();

		while (resultSet.next()) {			
			String columnName = resultSet.getString("COLUMN_NAME");
			
			if (columnName.matches(this.columnNamePattern)) {
				l.add(columnName);
			}			
		}
		DbUtils.closeQuietly(resultSet);
		return l;
	}

}
